﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using KPIS.GERP.GAMS.MODEL;
using KPIS.GERP.GAMS.DAL;
using Telerik.Web.UI;
using System.Collections.Generic;
using System.Globalization;
//Adding
using System.Data;
using System.Data.SqlClient;

namespace KPIS.GERP.GAMS.APP.App_UI.Forms
{
    public partial class ReportVatBuy : System.Web.UI.Page
    {
        Commons.Main main = new Commons.Main();
        Commons.MailMerge mailMerge = new Commons.MailMerge();
        DateTime dStartDate = DateTime.Today;
        DateTime dEndDate = DateTime.Today;
        string sStartDate = DateTime.Today.ToLongDateString();
        string sEndDate = DateTime.Today.ToLongDateString();
        D_ACM_Finance_Payment Payment = new D_ACM_Finance_Payment();
        D_BGM_MAS_Source source = new D_BGM_MAS_Source();
        CultureInfo _thCulture = new CultureInfo("th-TH");
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                UpdateUI1("Load");
                ddlBgmMonth.SelectedIndex = 0;
                txtBgmYear.Text = source.GetCurrentBgmYear();
            }
        }
        public void UpdateUI1(string strCommand)
        {

            switch (strCommand)
            {
                case "Load":



                    for (int i = 1; i <= 12; i++)
                    {

                        DateTime Tempdate = new DateTime(DateTime.Now.Year, i, 1);
                        RadComboBoxItem item = new RadComboBoxItem();
                        item.Text = Tempdate.ToString("MMMM", _thCulture);
                        item.Value = Convert.ToInt32(i).ToString();
                        ddlBgmMonth.Items.Add(item);

                        item.DataBind();
                    }

                    break;
            }
        }
        protected void btnReport2_Click(object sender, EventArgs e)
        {
            string selectdate = ddlBgmMonth.SelectedValue; //เดือนที่เลือก
            string strMonth = ddlBgmMonth.Text;
            string strBgmYear = txtBgmYear.Text;
            LoadReport(selectdate, strBgmYear, strMonth);
        }

        private void LoadReport(string selectdate, string strBgmYear,string strMonth)
        {
           
            int? month  = Convert.ToInt32(selectdate);
            
            int? year = Convert.ToInt32(strBgmYear);


            DataTable dt1 = ReportTaxPay(month.Value, year.Value);
            

            Dictionary<string, DataTable> dtbs = new Dictionary<string, DataTable>();

             dtbs.Add("Buy!A12", dt1);

             int sumRow = 12 + dt1.Rows.Count;
             double h = 0;
             double i = 0;
             double j = 0;

             foreach (DataRow r in dt1.Rows)
             {
                 h += double.Parse(r["amount"].ToString());
                 i += double.Parse(r["amountExcludeVat"].ToString());
                 j += double.Parse(r["Vat"].ToString());
             }

             string strDate = "เดือนที่คิดภาษี" + " " + strMonth + " " + "ปี " + strBgmYear;
            Dictionary<string, string> selDate = new Dictionary<string, string>();
            selDate.Add("Buy!C4", strDate);
            selDate.Add(string.Format("Buy!G{0}", sumRow), "ยอดรวม ");
            selDate.Add(string.Format("Buy!H{0}", sumRow), h.ToString("#,##0.00"));
            selDate.Add(string.Format("Buy!I{0}", sumRow), i.ToString("#,##0.00"));
            selDate.Add(string.Format("Buy!J{0}", sumRow), j.ToString("#,##0.00"));
           

            mailMerge.MergeExcel(Response, Server.MapPath("~/Reports/Templates/VatBuy.xlsx"), string.Format("BlanceSheet{0}", DateTime.Now.ToString("yyyyMMddHHmmssffff")), dtbs, selDate);
        }

        public DataTable ReportTaxPay(int? month, int? year)
        {
            int engYear = year.Value - 543;
            DateTime dtFrom = new DateTime(engYear, month.Value, 1);
            DateTime dtTo = dtFrom.AddMonths(1).AddDays(-1);
            int lastDate = dtTo.Day;
            string startDate = string.Format("{0}-{1}-{2}", engYear, month, 1);
            string endDate = string.Format("{0}-{1}-{2}", engYear, month, lastDate);

            string strConn = System.Web.Configuration.WebConfigurationManager.AppSettings["ConnectionString"];
            DataTable dt = new DataTable();

            string strSQL = string.Format("select ROW_NUMBER()OVER(ORDER BY a.pay_seq DESC) AS Row ,a.pay_date as real_pay_date,'-',d.NameTh  as ConName,d.TaxCode as TaxConCode,'-','-',a.amount as amount"
                                         + " ,(case isnull(a.vat,0) when 0 then 0 else (coalesce(a.amount, 0) - coalesce(a.vat, 0)) END )as amountExCludeVat ,isnull(a.vat,0) as Vat"
                                         + " from fim_payments a left join fim_vat_dtls vd on a.pay_seq = vd.pay_seq"
		                                 + " left join fim_vat v on vd.vat_seq = v.vat_seq and v.record_status <> 'D'"
                                          + " left join IcmMasContact d on d.ConId = a.contact_seq"
                                         + " where a.record_status = 'A' and coalesce(a.vat, 0) > 0 and a.bgm_source_seq = 11"
		                                 + " and a.pay_date between '{0}' and '{1}' and (vd.vat_seq is null)",startDate,endDate);

            using (SqlConnection conn = new SqlConnection(strConn))
            {
                using (SqlDataAdapter adp = new SqlDataAdapter(strSQL, conn))
                {
                    adp.Fill(dt);
                }
            }

            return dt;
        }
                  
    }
}